<?php
/**
 * [LupMisNotAllowedSync]
 * 本代码为系统自动生成代码，请根据自己业务进行修改;
 * 生成时间 2022-02-01 22:12:12;
 * 版权所有 2020-2022 lizhongwen，并保留所有权利;
 * 说明: 这不是一个自由软件！您只能在不用于商业目的的前提下对程序代码进行修改和使用；不允许对程序代码以任何形式任何目的的再发布;
 * 作者: 中文Lee;
 * 作者主页: http://www.lizhongwen.com;
 * 邮箱: 360811363@qq.com;
 * QQ: 360811363;
 */

namespace application\core;

use Exception;
use PDO;
use PDOException;
use PDOStatement;

class Db
{

    public static $sth = null;

    public static $lastSQL = '';

    public static $pdo = null;

    protected static $guid = 0;

    public static $link = '';

    public static function pdo(): PDO
    {
        if (self::$pdo !== null) {
            return self::$pdo;
        }
        try {
            $config = json_decode(DB_CONFIG, true);
            $dsn = sprintf('mysql:host=%s;dbname=%s;charset=utf8', $config[self::$link]['host'], $config[self::$link]['dbName']);
            $option = array(
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
            );
            if ($config['persistent']) {
                $option = array(
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_PERSISTENT => true
                );
            }
            return self::$pdo = new PDO($dsn, $config[self::$link]['userName'], $config[self::$link]['password'], $option);
        } catch (PDOException $e) {
            exit($e->getMessage());
        }
    }

    /**
     * 获取实际执行的sql
     * @param $sql
     * @param $data
     * @return string
     */
    public static function getExecSqlStr($sql, $data): string
    {
        $sql = preg_replace("/\s(?=\s)/", "\\1", $sql);
        $reSql = "";
        $isMap = strstr($sql, '#VALUES#');
        if ($isMap) {
            $sqlArr = [];
            foreach ($data as $k => $v) {
                foreach ($v as $_v) {
                    $sqlArr[$k][] = "'" . addslashes($_v) . "'";
                }
            }
            $_arr = [];
            foreach ($sqlArr as $v) {
                $_arr[] = implode(",", $v);
            }
            foreach ($_arr as $v) {
                $reSql .= str_replace('#VALUES#', 'VALUES(' . $v . ')', $sql) . ";\r\n";
            }
        } else {
            $reSql = $sql . ",";
            foreach ($data as $k => $v) {
                $reSql = str_replace(':' . $k . ",", "'" . addslashes($v) . "',", $reSql) . "";
                $reSql = str_replace(':' . $k . " ,", "'" . addslashes($v) . "',", $reSql) . "";
                $reSql = str_replace(':' . $k . ")", "'" . addslashes($v) . "')", $reSql) . "";
                $reSql = str_replace(':' . $k . " )", "'" . addslashes($v) . "')", $reSql) . "";
                $reSql = str_replace(':' . $k . " ", "'" . addslashes($v) . "' ", $reSql) . "";
            }
            $reSql = rtrim($reSql, ",");
            $reSql .= ";#CUT_SQL_BY_LINE#\r\n";
        }
        return $reSql;
    }

    public static function beginTransaction($link = 'default'): bool
    {
        self::setPDO($link);
        return self::pdo()->beginTransaction();
    }

    public static function inTransaction($link = 'default'): bool
    {
        self::setPDO($link);
        return self::pdo()->inTransaction();
    }

    public static function rollBack($link = 'default'): bool
    {
        self::setPDO($link);
        return self::pdo()->rollBack();
    }

    public static function commit($link = 'default'): bool
    {
        self::setPDO($link);
        return self::pdo()->commit();
    }


    /**
     * @throws Exception
     */
    public static function watchException($executeState)
    {
        if (!$executeState) {
            throw new Exception("SQL:" . self::$lastSQL . "\n" . self::$sth->errorInfo()[2], intval(self::$sth->errorCode()));
        }
    }

    /**
     * 获取所有数据
     */
    public static function fetchAll(string $sql, array $parameters = [], string $link = 'default'): array
    {
        self::setPDO($link);
        $sql = self::replaceSql($sql, $parameters);
        self::$lastSQL = $sql;
        self::$sth = self::pdo()->prepare($sql);
        self::bindValue($sql, $parameters);
        self::watchException(self::$sth->execute());
        return self::$sth->fetchAll();
    }

    /**
     * 分页
     *
     * @param string $sql
     * @param string $sqlCount
     * @param array $parameters
     * @param string $page
     * @param string $limit
     * @param bool $cusSql
     * @param int $PAGE_LIMIT_MAXTOTAL
     * @param string $link
     * @return array[]
     * @throws Exception
     */
    public static function page(string $sql, string $sqlCount, array $parameters = [], string $page = '', string $limit = '', bool $cusSql = false, int $PAGE_LIMIT_MAXTOTAL = PAGE_LIMIT_MAXTOTAL, string $link = 'default'): array
    {
        $page = empty($page) ? Request::request('page') : $page;
        $limit = empty($limit) ? Request::request('limit', 30) : $limit;
        if ($sqlCount == false) {
            $totalCount = $PAGE_LIMIT_MAXTOTAL;
        } else {
            $sqlCount1 = str_replace("COUNT(1)", "1", $sqlCount);
            $sqlCount1 = str_replace("count(1)", "1", $sqlCount1);
            $sqlCount1 = str_replace("count(*)", "1", $sqlCount1);
            $sqlCount1 = str_replace("COUNT(*)", "1", $sqlCount1);
            $sqlCount1 .= " LIMIT " . $PAGE_LIMIT_MAXTOTAL . ",1";
            $maxTotal = Db::fetch($sqlCount1, $parameters);

            if (!empty($maxTotal)) {
                $totalCount = $PAGE_LIMIT_MAXTOTAL;
            } else {
                $totalCount = self::fetchColumn($sqlCount, $parameters);
            }
        }

        $pageCount = ceil($totalCount / $limit);
        $page = ($page <= 1) ? 1 : $page;
        $page = ($page >= $pageCount) ? $pageCount : $page;
        $isFirstPage = ($page <= 1) ? 1 : -1;
        $isLastPage = ($page >= $pageCount) ? 1 : -1;
        $start = intval($page - 1) * $limit;
        if ($start <= 0) {
            $start = 0;
        }

        if ($cusSql == false) {
            $sql .= " LIMIT $start,$limit";
        } else {
            # LIMIT#
            // select * from `test_article_recycle` as a inner join ( select id from test_article_recycle where articleType=1 and title like '美%' limit 100000,100) b using (id) order by a.timestamp desc
            $sql = str_replace("#LIMIT#", "LIMIT $start,$limit", $cusSql);
        }
        $rows = self::fetchAll($sql, $parameters, $link);
        return [
            'page' => intval($page),
            'limit' => intval($limit),
            'total' => intval($totalCount),
            'pageCount' => intval($pageCount),
            'isFirstPage' => $isFirstPage,
            'isLastPage' => $isLastPage,
            'rows' => $rows
        ];
    }

    public static function replaceSql($sql, $data)
    {
        if (!empty($data['ORDER'])) {
            if (strpos($sql, '#ORDER#') !== false) {
                $sql = str_replace('#ORDER#', $data['ORDER'], $sql);
            }
        }

        if (!empty($data['GROUP']) !== false) {
            if (strpos($sql, '#GROUP#')) {
                $sql = str_replace('#GROUP#', $data['GROUP'], $sql);
            }
        }
        if (!empty($data['HAVING']) !== false) {
            if (strpos($sql, '#HAVING#')) {
                $sql = str_replace('#HAVING#', $data['HAVING'], $sql);
            }
        }
        return $sql;
    }

    /**
     * 获取一条记录
     *
     * @param string $sql
     * @param array $parameters
     * @param string $link
     * @return mixed
     * @throws Exception
     */
    public static function fetch(string $sql, array $parameters = [], string $link = 'default')
    {
        self::setPDO($link);
        $sql = self::replaceSql($sql, $parameters);
        self::$lastSQL = $sql;
        self::$sth = self::pdo()->prepare($sql);
        self::bindValue($sql, $parameters);
        self::watchException(self::$sth->execute());
        // self::$sth->debugDumpParams();
        return self::$sth->fetch();
    }

    /**
     * 获取一个字段
     */
    public static function fetchColumn(string $sql, array $parameters = [], string $link = 'default')
    {
        self::setPDO($link);
        $sql = self::replaceSql($sql, $parameters);
        self::$lastSQL = $sql;
        self::$sth = self::pdo()->prepare($sql);
        self::bindValue($sql, $parameters);
        self::watchException(self::$sth->execute());
        // self::$sth->debugDumpParams();
        // self::watchException(self::$sth->execute($parameters));
        return self::$sth->fetch(PDO::FETCH_NUM)[0];
    }

    public static function add($table, $parameters, $link = 'default'): bool
    {
        $fd = [];
        $bind = [];
        foreach ($parameters as $k => $v) {
            unset($v);
            $fd[] = '`' . $k . "`";
            $bind[] = ":" . $k;
        }
        $sql = "INSERT INTO `$table`(" . implode(',', $fd) . ") VALUE(" . implode(',', $bind) . ")";
        $ret = self::query($sql, $parameters, $link);
        if ($ret->rowCount() > 0) {
            return true;
        } else {
            return false;
        }
    }

    public static function upd($table, $parameters, string $link = 'default'): bool
    {
        $fd = [];
        $arr = $parameters;
        unset($arr['id']);
        foreach ($arr as $k => $v) {
            unset($v);
            $fd[] = '`' . $k . "`=:" . $k;
        }
        $sql = "UPDATE  `$table` set " . implode(',', $fd) . " where id=:id";
        $ret = self::query($sql, $parameters, $link);
        if ($ret->rowCount() > 0) {
            return true;
        } else {
            return false;
        }
    }

    public static function del($table, $id, $link = 'default'): bool
    {
        $sql = "DELETE FROM `$table` where id=:id";
        $ret = self::query($sql, [
            'id' => $id
        ], $link);
        if ($ret->rowCount() > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * @throws Exception
     */
    public static function query($sql, $parameters = [], $link = 'default')
    {
        // $sql = str_replace(' ', ' ', $sql); //替换连续的空格为一个
        // $sql = preg_replace("/\s(?=\s)/","\\1",$sql);
        self::setPDO($link);
        $sql = self::replaceSql($sql, $parameters);
        self::$lastSQL = $sql;
        self::$sth = self::pdo()->prepare($sql);
        self::bindValue($sql, $parameters);
        self::watchException(self::$sth->execute());
        // self::$sth->debugDumpParams();
        // self::watchException(self::$sth->execute($parameters));
        return self::$sth;
    }

    /**
     * @throws Exception
     */
    public static function insert($sql, $data, $link = 'default'): int
    {
        self::setPDO($link);

        $isMap = strstr($sql, '#VALUES#');
        if ($isMap) {
            $sql = str_replace('#VALUES#', 'VALUES', $sql);
            $sql = $sql . self::formatInsert($data);
        }
        self::$lastSQL = $sql;
        self::$sth = self::pdo()->prepare($sql);
        if ($isMap) {
            try {
                self::$sth = self::formatParam(self::$sth, $data);
            } catch (Exception $e) {
                echo $e;
            }
        } else {
            self::watchException(self::$sth->execute());
        }

        return self::$sth->rowCount();
    }

    public static function errorInfo()
    {
        return self::$sth->errorInfo();
    }

    protected static function mapKey(): string
    {
        return ':lzw_' . self::$guid++ . '_wzl';
    }

    protected static function typeMap($type)
    {
        $map = [
            'NULL' => PDO::PARAM_NULL,
            'integer' => PDO::PARAM_INT,
            'double' => PDO::PARAM_STR,
            'boolean' => PDO::PARAM_BOOL,
            'string' => PDO::PARAM_STR,
            'object' => PDO::PARAM_STR,
            'resource' => PDO::PARAM_LOB
        ];
        return $map[$type];
    }

    private static function bindValue($sql, $data)
    {
        if (empty($data)) {
            return;
        }
        $fdAll = [];
        foreach ($data as $k => $v) {
            if (is_array($v)) {
                foreach ($v as $kk => $vv) {
                    if (stripos($k, '#Map')) {
                        $map = str_replace('#Map', '', $k);
                        // $map = \explode(':Map', $k);
                        $fdAll[$map . '_lzw_' . $kk . '_wzl_' . $map] = $vv;
                    } else {
                        $fdAll[$kk] = $vv;
                    }
                }
            } else {
                if (stripos($k, '#Map')) {
                    // $map = \explode(':Map', $k);
                    $map = str_replace('#Map', '', $k);
                    $arr = explode(',', $v);
                    foreach ($arr as $sk => $sv) {
                        $fdAll[$map . '_lzw_' . $sk . '_wzl_' . $map] = $sv;
                    }
                } else {
                    $fdAll[$k] = $v;
                }
            }
        }
        $pattern = '/:.*?\b\w+/';
        preg_match_all($pattern, $sql, $arr);
        foreach ($arr[0] as $v) {
            $fd = ltrim($v, ':');
            $datas[$fd] = $fdAll[$fd];
        }
        if (empty($datas)) {
            return;
        }
        foreach ($datas as $k => $v) {
            if (is_array($data[$k])) {
                if ($k != 'ORDER' && $k != 'GROUP') {
                    foreach ($data[$k] as $kk => $vv) {
                        $dataType = gettype($vv);
                        $pdoType = self::typeMap($dataType);
                        self::$sth->bindValue($kk, $vv, $pdoType);
                    }
                }
            } else {
                if ($k != 'ORDER' && $k != 'GROUP' && $k != "00") {
                    $dataType = gettype($v);
                    $pdoType = self::typeMap($dataType);
                    self::$sth->bindValue($k, $v, $pdoType);
                }
            }
        }
    }

    // 将数组转换成插入格式的sql语句
    private static function formatInsert($data): string
    {
        $fd = [];
        $val = [];
        $vals = [];
        self::$guid = 0;
        $i = 0;
        foreach ($data as $k => $v) {
            if (is_array($v)) {
                if ($i == 0) {
                    foreach ($v as $kk => $vv) {
                        $fd[] = sprintf("`%s`", ltrim($kk, ':'));
                    }
                }
                foreach ($v as $vv) {
                    $map = self::mapKey();
                    $vals[$k][$map] = $vv;
                }
            } else {
                $fd[] = sprintf("`%s`", ltrim($k, ':'));
                $map = self::mapKey();
                $vals[0][$map] = $v;
            }
            $i++;
        }

        $i = 0;
        foreach ($vals as $v) {
            $val[$i] = '(' . implode(',', array_keys($v)) . ')';
            $i++;
        }
        unset($fd);
        return implode(',', $val);
    }

    /**
     * 占位符绑定具体的变量值
     * @param PDOStatement $sth
     * @param array $data
     * @return PDOStatement //PDOStatement
     * @throws Exception
     */
    public static function formatParam(PDOStatement $sth, array $data = []): PDOStatement
    {
        $i = 0;
        self::$guid = 0;
        foreach ($data as $v) {
            if (is_array($v)) {
                foreach ($v as $vv) {
                    $map = self::mapKey();
                    $sth->bindValue($map, $vv);
                }
            } else {
                $map = self::mapKey();

                $sth->bindValue($map, $v);
            }
            $i++;
        }
        self::watchException($sth->execute());
        return $sth;
    }

    public static function setPDO($link)
    {
        if (self::$link != null) {
            if (self::$link != $link) {
                self::$pdo = null;
            }
        }
        self::$link = $link;
    }

    function errorCode()
    {
        return self::$sth->errorCode();
    }
}